/**
 * Copyright (C) 2016 Baidu, Inc. All Rights Reserved.
 */
package com.huifei.parseexcel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import org.apache.commons.io.IOUtils;
import org.apache.commons.io.output.FileWriterWithEncoding;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * @author lihui29
 */
public class ParserExcel {
    public void parser(String desSql, String souExcel){
        try {
            InputStream is = new FileInputStream(souExcel);
            XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
            File dmlSql = new File(desSql + "_dml.sql");
            if (!dmlSql.exists()) {
                dmlSql.createNewFile();
            }
            FileWriterWithEncoding dmlFw = null;
            dmlFw = new FileWriterWithEncoding(dmlSql, "gbk");
            
            dmlFw.write("/*!40101 SET NAMES utf8 */;" + "\n");
            dmlFw.write("USE `sgw_config_db`;" + "\n");
            // 循环工作表Sheet
            System.out.println(hssfWorkbook.getNumberOfSheets());
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
                if (hssfSheet == null) {
                    IOUtils.closeQuietly(dmlFw);
                    return;
                }
                // 循环行Row
                System.out.println(hssfSheet.getLastRowNum());
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    String response_code = null;
                    String response_message = null;
                    String result_code = null;
                    String result_message = null;
                    String trans_result = null;
                    XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    
                    XSSFCell a = hssfRow.getCell(0);
                    //HSSFCell xh = "test";
                    if (a == null) {
                        continue;
                    }
                    a.setCellType(Cell.CELL_TYPE_STRING);
                    response_code = a.getStringCellValue();
                    //merchantID = "test";
                    XSSFCell xm = hssfRow.getCell(1);
                    if (xm == null) {
                        continue;
                    }
                    xm.setCellType(Cell.CELL_TYPE_STRING);
                    response_message = xm.getStringCellValue();
                    
                    XSSFCell b = hssfRow.getCell(2);
                    if (b == null) {
                        continue;
                    }
                    b.setCellType(Cell.CELL_TYPE_STRING);
                    result_code = b.getStringCellValue();
                    
                    XSSFCell c = hssfRow.getCell(3);
                    if (c == null) {
                        continue;
                    }
                    c.setCellType(Cell.CELL_TYPE_STRING);
                    result_message = c.getStringCellValue();
                    
                    XSSFCell d = hssfRow.getCell(4);
                    if (d == null) {
                        continue;
                    }
                    d.setCellType(Cell.CELL_TYPE_STRING);
                    trans_result = d.getStringCellValue();
                    
                    if (StringUtils.isNotBlank(result_code) && StringUtils.isNotBlank(result_message) &&
                    		StringUtils.isNotBlank(response_code) && StringUtils.isNotBlank(response_message) &&
                    		StringUtils.isNotBlank(trans_result)) {
                    	
                    	dmlFw.write("insert into sgw_channel_response_code (result_code_group_id,result_code_group_name,trans_result,response_code,response_message,result_code,result_message,GMT_CREATE,GMT_MODIFIED) "
                        		+ "values ('RC-CMBC090101-01','民生代扣错误码',\'" + trans_result + "\',\'" + response_code + "\',\'" + response_message + "\',\'" + result_code + "\',\'" + result_message + "\',now(),now());" + "\n");
                        
                    	
                    }
                    
                    

                }
            }
            
            dmlFw.flush();
            IOUtils.closeQuietly(dmlFw);
           
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        finally {
//            IOUtils.closeQuietly(dmlFw);
//            IOUtils.closeQuietly(dmlRollFw);
//            IOUtils.closeQuietly(zos);
//            dmlSql.delete();
        }
    }
}
